using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Common;
using System.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using Fieldbook.Entities;
using Fieldbook.Common;
namespace Fieldbook.DAL
{
	public static class MessageDAO
	{
		#region Insert
		private static DbCommand insertCommand = null;
		private static string insertSQL = "INSERT INTO dbo.Message (SENDERID, RECEIVERID, CAPTION, CONTENT, MESSAGETYPEID, MESSAGESTATUSID, MESSAGEREADSTATUS) VALUES (@SENDERID, @RECEIVERID, @CAPTION, @CONTENT, @MESSAGETYPEID, @MESSAGESTATUSID, @MESSAGEREADSTATUS) SET @MESSAGEID = SCOPE_IDENTITY()";
		private static DbCommand GetInsertCommand(SqlDatabase db)
		{
			if (insertCommand == null)
			{
				insertCommand = db.GetSqlStringCommand(insertSQL);
				db.AddOutParameter(insertCommand, "@MESSAGEID", DbType.Int64, 8);
				db.AddInParameter(insertCommand, "@SENDERID", DbType.Int64);
                db.AddInParameter(insertCommand, "@RECEIVERID", DbType.Int64);
				db.AddInParameter(insertCommand, "@CAPTION", DbType.String);
				db.AddInParameter(insertCommand, "@CONTENT", DbType.String);
				db.AddInParameter(insertCommand, "@MESSAGETYPEID", DbType.Byte);
				db.AddInParameter(insertCommand, "@MESSAGESTATUSID", DbType.Byte);
				db.AddInParameter(insertCommand, "@MESSAGEREADSTATUS", DbType.Boolean);
			}
			return insertCommand;
		}
		private static void SetInsertValues(SqlDatabase db, DbCommand cmd, Message entity)
		{
			db.SetParameterValue(cmd, "@SENDERID", entity.Senderid);
			db.SetParameterValue(cmd, "@RECEIVERID", entity.Receiverid);
			db.SetParameterValue(cmd, "@CAPTION", entity.Caption);
			db.SetParameterValue(cmd, "@CONTENT", entity.Content);
			db.SetParameterValue(cmd, "@MESSAGETYPEID", entity.Messagetypeid);
			db.SetParameterValue(cmd, "@MESSAGESTATUSID", entity.Messagestatusid);
			db.SetParameterValue(cmd, "@MESSAGEREADSTATUS", entity.Messagereadstatus);
		}
		public static bool InsertMessage(Message entity)
		{
			bool res = false;
			SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetConfigValue(Constants.FieldbookConnectionString));
			DbCommand cmd = GetInsertCommand(db);
			SetInsertValues(db, cmd, entity);
			res = Convert.ToBoolean(db.ExecuteNonQuery(cmd));
			if (res)
			{
				entity.Messageid = (long?)db.GetParameterValue(cmd, "@MESSAGEID");
			}
			return res;
		}
		public static bool InsertMessage(Message entity, DbTransaction transactionObj, SqlDatabase db)
		{
			bool res = false;
			DbCommand cmd = GetInsertCommand(db);
			SetInsertValues(db, cmd, entity);
			res = Convert.ToBoolean(db.ExecuteNonQuery(cmd, transactionObj));
			if (res)
			{
				entity.Messageid = (long?)db.GetParameterValue(cmd, "@MESSAGEID");
			}
			return res;
		}
		public static bool InsertListMessage(List<Message> list)
		{
			bool res = true;
			DbTransaction transactionObj = null;
			DbConnection conn = null;
			try
			{
				SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetConfigValue(Constants.FieldbookConnectionString));
				DbCommand cmd = GetInsertCommand(db);
				conn = db.CreateConnection();
				conn.Open();
				transactionObj = conn.BeginTransaction();
				for(int i=0; i<list.Count; i++)
				{
					Message entity = list[i];
					SetInsertValues(db, cmd, entity);
					res = res & Convert.ToBoolean(db.ExecuteNonQuery(cmd, transactionObj));
					if (res)
					{
						entity.Messageid = (long?)db.GetParameterValue(cmd, "@MESSAGEID");
					}
				}
				transactionObj.Commit();
				transactionObj.Dispose();
				conn.Close();
				conn.Dispose();
			}
			catch (Exception ex)
			{
				res = false;
				if (transactionObj != null)
				{
					transactionObj.Rollback();
					transactionObj.Dispose();
				}
				if (conn != null & conn.State != ConnectionState.Closed)
				{
					conn.Close();
					conn.Dispose();
				}
				throw ex;
			}
			return res;
		}
		public static bool InsertListMessage(List<Message> list, DbTransaction transactionObj, SqlDatabase db)
		{
			bool res = true;
			DbCommand cmd = GetInsertCommand(db);
			for(int i=0; i<list.Count; i++)
			{
				Message entity = list[i];
				SetInsertValues(db, cmd, entity);
				res = res & Convert.ToBoolean(db.ExecuteNonQuery(cmd, transactionObj));
				if (res)
				{
					entity.Messageid = (long?)db.GetParameterValue(cmd, "@MESSAGEID");
				}
			}
			return res;
		}
		#endregion
		#region Update
		private static DbCommand updateCommand = null;
		private static string updateSQL = "UPDATE dbo.Message SET SENDERID = @SENDERID, RECEIVERID = @RECEIVERID, CAPTION = @CAPTION, CONTENT = @CONTENT, MESSAGETYPEID = @MESSAGETYPEID, MESSAGESTATUSID = @MESSAGESTATUSID, MESSAGEREADSTATUS = @MESSAGEREADSTATUS WHERE MESSAGEID = @MESSAGEID";
		private static DbCommand GetUpdateCommand(SqlDatabase db)
		{
			if (updateCommand == null)
			{
				updateCommand = db.GetSqlStringCommand(updateSQL);
				db.AddInParameter(updateCommand, "@MESSAGEID", DbType.Int64);
                db.AddInParameter(updateCommand, "@SENDERID", DbType.Int64);
                db.AddInParameter(updateCommand, "@RECEIVERID", DbType.Int64);
				db.AddInParameter(updateCommand, "@CAPTION", DbType.String);
				db.AddInParameter(updateCommand, "@CONTENT", DbType.String);
				db.AddInParameter(updateCommand, "@MESSAGETYPEID", DbType.Byte);
				db.AddInParameter(updateCommand, "@MESSAGESTATUSID", DbType.Byte);
				db.AddInParameter(updateCommand, "@MESSAGEREADSTATUS", DbType.Boolean);
			}
			return updateCommand;
		}
		private static void SetUpdateValues(SqlDatabase db, DbCommand cmd, Message entity)
		{
			db.SetParameterValue(cmd, "@MESSAGEID", entity.Messageid);
			db.SetParameterValue(cmd, "@SENDERID", entity.Senderid);
			db.SetParameterValue(cmd, "@RECEIVERID", entity.Receiverid);
			db.SetParameterValue(cmd, "@CAPTION", entity.Caption);
			db.SetParameterValue(cmd, "@CONTENT", entity.Content);
			db.SetParameterValue(cmd, "@MESSAGETYPEID", entity.Messagetypeid);
			db.SetParameterValue(cmd, "@MESSAGESTATUSID", entity.Messagestatusid);
			db.SetParameterValue(cmd, "@MESSAGEREADSTATUS", entity.Messagereadstatus);
		}
		public static bool UpdateMessage(Message entity)
		{
			bool res = false;
			SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetConfigValue(Constants.FieldbookConnectionString));
			DbCommand cmd = GetUpdateCommand(db);
			SetUpdateValues(db, cmd, entity);
			res = Convert.ToBoolean(db.ExecuteNonQuery(cmd));
			return res;
		}
		public static bool UpdateMessage(Message entity, DbTransaction transactionObj, SqlDatabase db)
		{
			bool res = false;
			DbCommand cmd = GetUpdateCommand(db);
			SetUpdateValues(db, cmd, entity);
			res = Convert.ToBoolean(db.ExecuteNonQuery(cmd, transactionObj));
			return res;
		}
		public static bool UpdateListMessage(List<Message> list)
		{
			bool res = true;
			DbTransaction transactionObj = null;
			DbConnection conn = null;
			try
			{
				SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetConfigValue(Constants.FieldbookConnectionString));
				DbCommand cmd = GetUpdateCommand(db);
				conn = db.CreateConnection();
				conn.Open();
				transactionObj = conn.BeginTransaction();
				for(int i=0; i<list.Count; i++)
				{
					Message entity = list[i];
					SetUpdateValues(db, cmd, entity);
					res = res & Convert.ToBoolean(db.ExecuteNonQuery(cmd, transactionObj));
				}
				transactionObj.Commit();
				transactionObj.Dispose();
				conn.Close();
				conn.Dispose();
			}
			catch (Exception ex)
			{
				res = false;
				if (transactionObj != null)
				{
					transactionObj.Rollback();
					transactionObj.Dispose();
				}
				if (conn != null & conn.State != ConnectionState.Closed)
				{
					conn.Close();
					conn.Dispose();
				}
				throw ex;
			}
			return res;
		}
		public static bool UpdateListMessage(List<Message> list, DbTransaction transactionObj, SqlDatabase db)
		{
			bool res = true;
			DbCommand cmd = GetUpdateCommand(db);
			for(int i=0; i<list.Count; i++)
			{
				Message entity = list[i];
				SetUpdateValues(db, cmd, entity);
				res = res & Convert.ToBoolean(db.ExecuteNonQuery(cmd, transactionObj));
			}
			return res;
		}
		
		#endregion
		#region Save
		public static bool SaveMessage(Message entity)
		{
			bool res = true;
			if (entity.Messageid == null )
			{
				res = InsertMessage(entity);
			}
			else
			{
				res = UpdateMessage(entity);
			}
			return res;
		}
		public static bool SaveMessage(Message entity, DbTransaction transactionObj, SqlDatabase db)
		{
			bool res = true;
			if (entity.Messageid == null )
			{
				res = InsertMessage(entity, transactionObj, db);
			}
			else
			{
				res = UpdateMessage(entity, transactionObj, db);
			}
			return res;
		}
		public static bool SaveListMessage(List<Message> list)
		{
			bool res = true;
			DbTransaction transactionObj = null;
			DbConnection conn = null;
			try
			{
				SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetConfigValue(Constants.FieldbookConnectionString));
				conn = db.CreateConnection();
				conn.Open();
				transactionObj = conn.BeginTransaction();
				for(int i=0; i<list.Count; i++)
				{
					Message entity = list[i];
					res = res & SaveMessage(entity, transactionObj, db);
				}
				transactionObj.Commit();
				transactionObj.Dispose();
				conn.Close();
				conn.Dispose();
			}
			catch (Exception ex)
			{
				res = false;
				if (transactionObj != null)
				{
					transactionObj.Rollback();
					transactionObj.Dispose();
				}
				if (conn != null & conn.State != ConnectionState.Closed)
				{
					conn.Close();
					conn.Dispose();
				}
				throw ex;
			}
			return res;
		}
		public static bool SaveListMessage(List<Message> list, DbTransaction transactionObj, SqlDatabase db)
		{
			bool res = true;
			for(int i=0; i<list.Count; i++)
			{
				Message entity = list[i];
				res = res & SaveMessage(entity, transactionObj, db);
			}
			return res;
		}
		#endregion
		#region Delete
        private static DbCommand GetDeleteCommand(SqlDatabase db)
        {
            DbCommand deleteCommand = null;
            string deleteSQL = "DELETE FROM dbo.Message WHERE MESSAGEID = @MESSAGEID";
            deleteCommand = db.GetSqlStringCommand(deleteSQL);
            db.AddInParameter(deleteCommand, "@MESSAGEID", DbType.Int64);
            return deleteCommand;
        }
		private static void SetDeleteValues(SqlDatabase db, DbCommand cmd, Message entity)
		{
			db.SetParameterValue(cmd, "@MESSAGEID", entity.Messageid);
		}
		public static bool DeleteMessage(Message entity)
		{
			bool res = false;
			SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetConfigValue(Constants.FieldbookConnectionString));
			DbCommand cmd = GetDeleteCommand(db);
			SetDeleteValues(db, cmd, entity);
			res = Convert.ToBoolean(db.ExecuteNonQuery(cmd));
			return res;
		}
		public static bool DeleteListMessage(List<Message> list)
		{
			bool res = true;
			DbTransaction transactionObj = null;
			DbConnection conn = null;
			try
			{
				SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetConfigValue(Constants.FieldbookConnectionString));
				DbCommand cmd = GetDeleteCommand(db);
				conn = db.CreateConnection();
				conn.Open();
				transactionObj = conn.BeginTransaction();
				for(int i=0; i<list.Count; i++)
				{
					Message entity = list[i];
					SetDeleteValues(db, cmd, entity);
					res = res & Convert.ToBoolean(db.ExecuteNonQuery(cmd, transactionObj));
				}
				transactionObj.Commit();
				transactionObj.Dispose();
				conn.Close();
				conn.Dispose();
			}
			catch (Exception ex)
			{
				res = false;
				if (transactionObj != null)
				{
					transactionObj.Rollback();
					transactionObj.Dispose();
				}
				if (conn != null & conn.State != ConnectionState.Closed)
				{
					conn.Close();
					conn.Dispose();
				}
				throw ex;
			}
			return res;
		}

        public static bool DeleteListMessage(List<long> list)
        {
            bool res = true;
            DbTransaction transactionObj = null;
            DbConnection conn = null;
            try
            {
                SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetConfigValue(Constants.FieldbookConnectionString));
                DbCommand cmd = GetDeleteCommand(db);
                conn = db.CreateConnection();
                conn.Open();
                transactionObj = conn.BeginTransaction();
                for (int i = 0; i < list.Count; i++)
                {
                    db.SetParameterValue(cmd, "@MESSAGEID", list[i]);
                    res = res & Convert.ToBoolean(db.ExecuteNonQuery(cmd, transactionObj));
                }
                transactionObj.Commit();
                transactionObj.Dispose();
                conn.Close();
                conn.Dispose();
            }
            catch (Exception ex)
            {
                res = false;
                if (transactionObj != null)
                {
                    transactionObj.Rollback();
                    transactionObj.Dispose();
                }
                if (conn != null & conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                    conn.Dispose();
                }
                throw ex;
            }
            return res;
        }
		#endregion
		#region Select
        
        private static List<Message> LoadMessage(string sql)
        {
            List<Message> res = new List<Message>();
            IDataReader reader = null;
            try
            {
                SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetConfigValue(Constants.FieldbookConnectionString));
                reader = db.ExecuteReader(CommandType.Text, sql);
                if (reader != null)
                {
                    while (reader.Read())
                    {
                        Message entity = new Message();
                        entity.Messageid = reader.IsDBNull(0) ? null : (long?)reader.GetInt64(0);
                        entity.Senderid = reader.IsDBNull(1) ? null : (int?)reader.GetInt64(1);
                        entity.Receiverid = reader.IsDBNull(2) ? null : (int?)reader.GetInt64(2);
                        entity.Caption = reader.IsDBNull(3) ? null : (string)reader.GetString(3);
                        entity.Content = reader.IsDBNull(4) ? null : (string)reader.GetString(4);
                        entity.Messagetypeid = (Message.MessageType)reader.GetByte(5);
                        entity.Messagestatusid = (Message.MessageStatus)reader.GetByte(6);
                        entity.Messagereadstatus = reader.IsDBNull(7) ? null : (bool?)reader.GetBoolean(7);
                        res.Add(entity);
                    }
                    reader.Close();
                }
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                {
                    reader.Close();
                }
                res = null;
                throw ex;
            }
            return res;
        }

		public static List<Message> LoadMessages(string where, string orderby)
		{
			string selectSQL = String.Format("SELECT MESSAGEID,SENDERID,RECEIVERID,CAPTION,CONTENT,MESSAGETYPEID,MESSAGESTATUSID,MESSAGEREADSTATUS FROM dbo.Message {0} {1}",
                                            where, orderby);
            return LoadMessage(selectSQL);
		}

        public static List<Message> LoadMessages(string where, string orderby, int maximumRows, int startRowIndex)
        {
            if (String.IsNullOrEmpty(orderby))
            {
                orderby = "MESSAGEID";
            }
            int endRowIndex = startRowIndex + maximumRows - 1;
            string selectSQL = String.Format(@"WITH MESSAGES AS (
SELECT ROW_NUMBER() OVER (ORDER BY {0} ASC) AS ROW, MESSAGEID,SENDERID,RECEIVERID,CAPTION,CONTENT,MESSAGETYPEID,MESSAGESTATUSID,MESSAGEREADSTATUS FROM dbo.Message {1}
                                            )
SELECT MESSAGEID,SENDERID,RECEIVERID,CAPTION,CONTENT,MESSAGETYPEID,MESSAGESTATUSID,MESSAGEREADSTATUS FROM MESSAGES WHERE ROW BETWEEN {2} AND {3}",
                                            orderby, where, startRowIndex, endRowIndex
                                            );
            return LoadMessage(selectSQL);

        }

        public static List<long> GetMessageIDs(string criteria)
        {
            List<long> res = new List<long>();
            string selectSql = String.Format("SELECT MESSAGEID FROM dbo.Message {0}", criteria);
            IDataReader reader = null;
            try
            {
                SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetConfigValue(Constants.FieldbookConnectionString));
                reader = db.ExecuteReader(CommandType.Text, selectSql);
                if (reader != null)
                {
                    while (reader.Read())
                    {
                        long id = reader.GetInt64(0);
                        res.Add(id);
                    }
                    reader.Read();
                }
            }
            catch (Exception ex)
            {
                if (reader != null && !reader.IsClosed)
                {
                    reader.Close();
                }
                throw ex;
            }
            return res;

        }

        public static int GetMessagesCount(string criteria)
        {
            string selectSql = String.Format("SELECT COUNT(MESSAGEID) AS msg_count FROM dbo.Message {0}", criteria);
            SqlDatabase db = new SqlDatabase(AppSettingsHelper.GetConfigValue(Constants.FieldbookConnectionString));
            int res = (int)db.ExecuteScalar(CommandType.Text, selectSql);
            return res;
        }

		#endregion
	}
}
